﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Drawing;

namespace ADOnetForm
{
    public static class ADOoperation
    {
        private static readonly string constr = "server=.;user=sa;pwd=123456;database=ShoolMessage";

        private static readonly SqlConnection myconn = new SqlConnection(constr);


        public static int Try { get; set; }



        //插入数据
        public static int Adut(string name, string avg, string sex)
        {
            SqlDataAdapter myadapter = null;

            DataTable mytable = new DataTable();

            Try = 1;

            string sql = "select * from Student";

            try
            {
                myconn.Open();

                myadapter = new SqlDataAdapter(sql, myconn);

                myadapter.Fill(mytable);

                SqlCommandBuilder mybuilder = new SqlCommandBuilder(myadapter);

                DataRow myrow = mytable.NewRow();//新增一行

                myrow["s_name"] = name;
                myrow["s_age"] = avg;
                myrow["s_sex"] = sex;

                mytable.Rows.Add(myrow);

                myadapter.Update(mytable);

            }
            catch (Exception e)
            {

                Try = -1;
                Console.WriteLine(e.Message);
            }
            finally
            {
                myconn.Close();
            }

            return Try;
        }

        //查询数据
        public static DataTable Select(string sql)
        {

            SqlDataAdapter myadapter = null;


            DataTable mytable = new DataTable();

            try
            {
                myconn.Open();

                myadapter = new SqlDataAdapter(sql, myconn);

                myadapter.Fill(mytable);
            }
            catch (Exception e)
            {

                Console.WriteLine(e.Message);
            }
            finally
            {
                myconn.Close();
            }

            return mytable;

        }

        //更新数据
        public static void Update(string id, string values,string strColumns)
        {
            string sql = "select * from Student";

            DataTable mytable = new DataTable();

            try
            {
                myconn.Open();

                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, myconn);

                dataAdapter.Fill(mytable);

                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

                DataColumn[] colmKey = new DataColumn[] { mytable.Columns["Id"]};

                mytable.PrimaryKey = colmKey;//把id列设为主键列

                DataRow myrow = mytable.Rows.Find(id);//指定行

                myrow[strColumns] = values;


                dataAdapter.Update(mytable);

            }
            catch (Exception e)
            {

                Console.WriteLine(e.Message);
            }
            finally
            {

                myconn.Close();
            }
        }


        //删除数据
        public static void DeleteRow(string id)
        {
            string sql = "select * from Student";

            DataTable mytable = new DataTable();

            try
            {
                myconn.Open();

                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, myconn);

                dataAdapter.Fill(mytable);

                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

                DataColumn[] colmKey = new DataColumn[] { mytable.Columns["Id"] };

                mytable.PrimaryKey = colmKey;//把id列设为主键列

                DataRow myrow = mytable.Rows.Find(id);//指定行

                myrow.Delete();

                dataAdapter.Update(mytable);

            }
            catch (Exception e)
            {

                Console.WriteLine(e.Message);
            }
            finally
            {

                myconn.Close();
            }
        }
    }
}
